
/****** Object:  StoredProcedure [proc_ProductsLoadByPrimaryKey]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_ProductsLoadByPrimaryKey]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_ProductsLoadByPrimaryKey];
GO

CREATE PROCEDURE [proc_ProductsLoadByPrimaryKey]
(
	@ProdID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[ProdID],
		[CatID],
		[ProdName],
		[ProdType],
		[ProdIsSoiree],
		[ProdColor],
		[ProdSize],
		[ProdQuantity],
		[ProdNetPrice],
		[ProdPrice]
	FROM [Products]
	WHERE
		([ProdID] = @ProdID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_ProductsLoadByPrimaryKey Succeeded'
ELSE PRINT 'Procedure Creation: proc_ProductsLoadByPrimaryKey Error on Creation'
GO

/****** Object:  StoredProcedure [proc_ProductsLoadAll]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_ProductsLoadAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_ProductsLoadAll];
GO

CREATE PROCEDURE [proc_ProductsLoadAll]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[ProdID],
		[CatID],
		[ProdName],
		[ProdType],
		[ProdIsSoiree],
		[ProdColor],
		[ProdSize],
		[ProdQuantity],
		[ProdNetPrice],
		[ProdPrice]
	FROM [Products]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_ProductsLoadAll Succeeded'
ELSE PRINT 'Procedure Creation: proc_ProductsLoadAll Error on Creation'
GO

/****** Object:  StoredProcedure [proc_ProductsUpdate]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_ProductsUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_ProductsUpdate];
GO

CREATE PROCEDURE [proc_ProductsUpdate]
(
	@ProdID int,
	@CatID int = NULL,
	@ProdName nvarchar(250) = NULL,
	@ProdType nvarchar(50) = NULL,
	@ProdIsSoiree bit = NULL,
	@ProdColor nvarchar(50) = NULL,
	@ProdSize nvarchar(50) = NULL,
	@ProdQuantity int = NULL,
	@ProdNetPrice float = NULL,
	@ProdPrice float = NULL
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	UPDATE [Products]
	SET
		[CatID] = @CatID,
		[ProdName] = @ProdName,
		[ProdType] = @ProdType,
		[ProdIsSoiree] = @ProdIsSoiree,
		[ProdColor] = @ProdColor,
		[ProdSize] = @ProdSize,
		[ProdQuantity] = @ProdQuantity,
		[ProdNetPrice] = @ProdNetPrice,
		[ProdPrice] = @ProdPrice
	WHERE
		[ProdID] = @ProdID


	SET @Err = @@Error


	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_ProductsUpdate Succeeded'
ELSE PRINT 'Procedure Creation: proc_ProductsUpdate Error on Creation'
GO




/****** Object:  StoredProcedure [proc_ProductsInsert]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_ProductsInsert]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_ProductsInsert];
GO

CREATE PROCEDURE [proc_ProductsInsert]
(
	@ProdID int,
	@CatID int = NULL,
	@ProdName nvarchar(250) = NULL,
	@ProdType nvarchar(50) = NULL,
	@ProdIsSoiree bit = NULL,
	@ProdColor nvarchar(50) = NULL,
	@ProdSize nvarchar(50) = NULL,
	@ProdQuantity int = NULL,
	@ProdNetPrice float = NULL,
	@ProdPrice float = NULL
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	INSERT
	INTO [Products]
	(
		[ProdID],
		[CatID],
		[ProdName],
		[ProdType],
		[ProdIsSoiree],
		[ProdColor],
		[ProdSize],
		[ProdQuantity],
		[ProdNetPrice],
		[ProdPrice]
	)
	VALUES
	(
		@ProdID,
		@CatID,
		@ProdName,
		@ProdType,
		@ProdIsSoiree,
		@ProdColor,
		@ProdSize,
		@ProdQuantity,
		@ProdNetPrice,
		@ProdPrice
	)

	SET @Err = @@Error


	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_ProductsInsert Succeeded'
ELSE PRINT 'Procedure Creation: proc_ProductsInsert Error on Creation'
GO

/****** Object:  StoredProcedure [proc_ProductsDelete]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_ProductsDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_ProductsDelete];
GO

CREATE PROCEDURE [proc_ProductsDelete]
(
	@ProdID int
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	DELETE
	FROM [Products]
	WHERE
		[ProdID] = @ProdID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_ProductsDelete Succeeded'
ELSE PRINT 'Procedure Creation: proc_ProductsDelete Error on Creation'
GO
